/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.report.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class BaocaoTonghopMT {
    public String doitac;
    public String dichvu;
    public int soMTGui;
    public int thanhcong;
    public int thatbai;
    public int khongcoThongtin;

    public BaocaoTonghopMT() {
    }

    public String getDoitac() {
        return doitac;
    }

    public void setDoitac(String doitac) {
        this.doitac = doitac;
    }

    public String getDichvu() {
        return dichvu;
    }

    public void setDichvu(String dichvu) {
        this.dichvu = dichvu;
    }

    public int getSoMTGui() {
        return soMTGui;
    }

    public void setSoMTGui(int soMTGui) {
        this.soMTGui = soMTGui;
    }

    public int getThanhcong() {
        return thanhcong;
    }

    public void setThanhcong(int thanhcong) {
        this.thanhcong = thanhcong;
    }

    public int getThatbai() {
        return thatbai;
    }

    public void setThatbai(int thatbai) {
        this.thatbai = thatbai;
    }

    public int getKhongcoThongtin() {
        return khongcoThongtin;
    }

    public void setKhongcoThongtin(int khongcoThongtin) {
        this.khongcoThongtin = khongcoThongtin;
    }
    
    public static List<BaocaoTonghopMT> getList(String tuNgay,String denNgay,int partner_id,int product_id) throws Exception{
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<BaocaoTonghopMT> list = new ArrayList<BaocaoTonghopMT>();
        
        try{
            conn = AppServer.getConnection();
            pst = conn.prepareCall("select '' partner_name, b.product_name,sum(a.actual_sms_count),sum(case when a.delivery_status in ('DeliveredToTerminal','DeliveredToNetwork','DeliveryUncertain') then a.actual_sms_count else 0 end) success_count,"
                    + " sum(case when a.delivery_status = 'DeliveryImpossible' then a.actual_sms_count else 0 end) fail_count,"
                    + " sum(case when a.delivery_status = '0' or delivery_status is null then a.actual_sms_count else 0 end)"
                    + " from sdp_sms_batches a, sdp_products b, sdp_users c "
                    + " where a.created_time >= to_date(?,'dd/mm/yyyy')"
                    + " and a.created_time < to_date(?,'dd/mm/yyyy') + 1"
                    + " and a.product_id = b.product_id "
                    + " and a.product_id = c.product_id and a.isdn = c.sdp_user_id "
                    + " and ( a.product_id = ? or ? = -1) "
                    + " and ( c.partner_id = ? or ? = -1)  "
                    + " group by b.product_name"
                    + " order by b.product_name");
            
            pst.setString(1, tuNgay);
            pst.setString(2, denNgay);
            pst.setInt(3, product_id);
            pst.setInt(4, product_id);
            pst.setInt(5, partner_id);
            pst.setInt(6, partner_id);
            rs = pst.executeQuery();
            
            while(rs.next()){
                BaocaoTonghopMT bc = new BaocaoTonghopMT();
                bc.setDoitac(rs.getString(1));
                bc.setDichvu(rs.getString(2));
                bc.setSoMTGui(Integer.parseInt(rs.getString(3)));
                bc.setThanhcong(Integer.parseInt(rs.getString(4)));
                bc.setThatbai(Integer.parseInt(rs.getString(5)));
                bc.setKhongcoThongtin(Integer.parseInt(rs.getString(6)));
                
                list.add(bc);
            }
            return list;
        }
        finally{            
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(conn);
        }
    }    
}
